﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MockPrjTeam1.Entity;
using SD.Commons.Helpers;
using System.Data;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using MockPrjTeam1.Biz;

namespace MockPrjTeam1.DAO
{
    public class FacilityDAO
    {
        public static SqlConnection Conn;
        public static string connString;
        public static void Connect()
        {
        }
        //public FacilityDAO()
        //{

        //    connString = "Data Source=localhost;Initial Catalog=ABSD_DB;Integrated Security=True";
        //    Conn = new SqlConnection();
        //    //Conn.ConnectionString = connString;
        //    Conn.Open();

        //}
        public List<Facility> GetAll()
        {
            var list = new List<Facility>();
            var results = SqlHelper.ExecuteReader("GetAllFacility");
            while (results.Read())
            {
                var item = new Facility();
                MappingField(item, results);
                if (item.LeadContactID != 0)
                {
                    ContactBiz contactBiz = new ContactBiz();
                    Contact contact = contactBiz.GetOne(item.LeadContactID);
                    if (contact != null)
                    {
                        item.LeadContactName = contact.ContactName;
                    }
                }

                if (item.RoomHostID != 0)
                {
                    ContactBiz contactBiz = new ContactBiz();
                    Contact contact = contactBiz.GetOne(item.RoomHostID);
                    if (contact != null)
                    {
                        item.RoomHostName = contact.ContactName;
                    }
                }
                list.Add(item);


            }
            return list;
        }

        public List<Facility> GetActive()
        {
            var list = new List<Facility>();
            var results = SqlHelper.ExecuteReader("GetActiveFacility");
             while (results.Read())
            {
                 var item = new Facility();
                 MappingField(item, results);
                if (item.LeadContactID != 0)
                {
                    ContactBiz contactBiz = new ContactBiz();
                    Contact contact = contactBiz.GetOne(item.LeadContactID);
                    if (contact != null)
                    {
                        item.LeadContactName = contact.ContactName;
                    }
                }
                if (item.RoomHostID != 0)
                {
                    ContactBiz contactBiz = new ContactBiz();
                    Contact contact = contactBiz.GetOne(item.RoomHostID);
                    if (contact != null)
                    {
                        item.RoomHostName = contact.ContactName;
                    }
                } list.Add(item);
            }
            return list;

        }
        public bool Save(Facility facility)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1",facility.FacilityType));
            temp.Add(new SqlParameter("p2", facility.Description));
            temp.Add(new SqlParameter("p3", facility.LeadContactID));
            temp.Add(new SqlParameter("p4", facility.RoomHostID));
            temp.Add(new SqlParameter("p5", facility.RoomCapacity));
            temp.Add(new SqlParameter("p6", facility.RoomSize));
            temp.Add(new SqlParameter("p7", facility.RoomConnectivity));
            temp.Add(new SqlParameter("p8", facility.ConnectivityType));
            temp.Add(new SqlParameter("p9", facility.WIAccessInfor));
            temp.Add(new SqlParameter("p10", facility.EquipmentAvailable));
            temp.Add(new SqlParameter("p11", facility.RQNote));
            temp.Add(new SqlParameter("p12", facility.Active));

            var result = SqlHelper.ExecuteNonQuery("CreateFacility", temp);
            if (result != 0)
            {
                return true;
            }
            else return false;
        }
        public static DataTable GetDataToTable(string sql)
        {
            SqlDataAdapter Mydata = new SqlDataAdapter();
            Mydata.SelectCommand = new SqlCommand();
            Mydata.SelectCommand.Connection = FacilityDAO.Conn;
            Mydata.SelectCommand.CommandText = sql;
            DataTable table = new DataTable();
            Mydata.Fill(table);
            return table;
        }
        public Facility GetOne(int id)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", id));

            var item = new Facility();
            var results = SqlHelper.ExecuteReader("GetOneFacility", temp);
            if (results.Read())
            { 
                    MappingField(item, results);
                  
                return item;
            }
            return null;
        }
        public bool CreateFacility(Facility Entity)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", Entity.FacilityType));
            temp.Add(new SqlParameter("p2", Entity.Description));
            temp.Add(new SqlParameter("p3", Entity.LeadContactID));
            temp.Add(new SqlParameter("p4", Entity.RoomHostID));
            temp.Add(new SqlParameter("p5", Entity.RoomCapacity));
            temp.Add(new SqlParameter("p6", Entity.RoomSize));
            temp.Add(new SqlParameter("p7", Entity.RoomConnectivity));
            temp.Add(new SqlParameter("p8", Entity.ConnectivityType));
            temp.Add(new SqlParameter("p9", Entity.WIAccessInfor));
            temp.Add(new SqlParameter("p10", Entity.EquipmentAvailable));
            temp.Add(new SqlParameter("p11", Entity.RQNote));
            temp.Add(new SqlParameter("p12", Entity.Active));

            var result = SqlHelper.ExecuteNonQuery("CreateFacility", temp);
            if (result != 0)
            {
                return true;
            }
            else return false;

        }


        public bool EditFacility(Facility Entity)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p0", Entity.FacilityID));
            temp.Add(new SqlParameter("p1", Entity.FacilityType));
            temp.Add(new SqlParameter("p2", Entity.Description));
            temp.Add(new SqlParameter("p3", Entity.LeadContactID));
            temp.Add(new SqlParameter("p4", Entity.RoomHostID));
            temp.Add(new SqlParameter("p5", Entity.RoomCapacity));
            temp.Add(new SqlParameter("p6", Entity.RoomSize));
            temp.Add(new SqlParameter("p7", Entity.RoomConnectivity));
            temp.Add(new SqlParameter("p8", Entity.ConnectivityType));
            temp.Add(new SqlParameter("p9", Entity.WIAccessInfor));
            temp.Add(new SqlParameter("p10", Entity.EquipmentAvailable));
            temp.Add(new SqlParameter("p11", Entity.RQNote));
            temp.Add(new SqlParameter("p12", Entity.Active));
            var result = SqlHelper.ExecuteNonQuery("EditFacility", temp);
            if (result != 0)
            {
                return true;
            }
            else return false;
        }
        public List<Facility> Filter(int filterID,int Active)
        {
            var list = new List<Facility>();
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", filterID));
            temp.Add(new SqlParameter("p2", Active));
            var results = SqlHelper.ExecuteReader("FilterFacility", temp);
            while (results.Read())
            {
                var item = new Facility();
                MappingField(item, results);
                list.Add(item);
            }
            return list;
        }

        private void MappingField(Facility item, IDataReader results)
        {
            item.FacilityID = results.GetInt32(0);
            item.FacilityType = results.GetString(1);
            item.Description = results.GetString(2);
            item.LeadContactID = results.GetInt32(3);
            item.RoomHostID = results.GetInt32(4);
            item.RoomCapacity = results.GetString(5);
            item.RoomSize = results.GetString(6);
            item.RoomConnectivity = results.GetBoolean(7);
            item.ConnectivityType = results.GetString(8);
            item.WIAccessInfor = results.GetString(9);
            item.EquipmentAvailable = results.GetBoolean(10);
            item.RQNote = results.GetString(11);
            item.Active = results.GetBoolean(12);
         
        }
    
    }
}